﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using Pixysoft.Framework.XSchEmA.Entity;
using System.Data;

namespace Pixysoft.Framework.XSchEmA.Database
{
    class SQLiteSchemaDownloader
    {
        private const int text_length = 2147483647;
        private const int int_length = 38;

        private static volatile SQLiteSchemaDownloader instance;

        private static object syncRoot = new Object();

        public static SQLiteSchemaDownloader Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        instance = new SQLiteSchemaDownloader();
                    }
                }

                return instance;

            }
        }

        public void GetTables(SQLiteConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Tables");

                foreach (DataRow row in table.Rows)
                {
                    string tabletype = row["TABLE_TYPE"].ToString().Trim().ToUpper();

                    string tablename = row["TABLE_NAME"].ToString().ToUpper().Trim();

                    if (tabletype != "TABLE")
                        continue;

                    TableSchema tableSchema = new TableSchema(tablename);
                    schema.Tables.Add(tableSchema);
                }
            }
        }

        public void GetColumns(SQLiteConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Columns");

                foreach (TableSchema tbschema in schema.Tables)
                {
                    foreach (DataRow row in Pixysoft.DataTableCoder.Instance.GetRows(table, "TABLE_NAME", tbschema.TableName))
                    {
                        string typename = row["DATA_TYPE"].ToString().Trim().ToUpper();

                        string colname = row["COLUMN_NAME"].ToString().Trim().ToUpper();

                        string defaultValue = row["COLUMN_DEFAULT"].ToString().Trim().ToUpper();

                        bool autoincrement = bool.Parse(row["AUTOINCREMENT"].ToString());

                        ColumnSchema columnSchema = new ColumnSchema();
                        columnSchema.TableName = tbschema.TableName;
                        columnSchema.ColumnName = colname;
                        columnSchema.IsNullable = row["IS_NULLABLE"].ToString().Trim() == "YES" ? true : false;
                        columnSchema.DefaultValue = defaultValue == "" ? null : defaultValue;

                        DataTypeSchema typeschema = new DataTypeSchema(typename,
                          row["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim() != "" ? Int64.Parse(row["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim()) : -1,
                          row["NUMERIC_PRECISION"].ToString().Trim() != "" ? int.Parse(row["NUMERIC_PRECISION"].ToString().Trim()) : -1,
                          row["NUMERIC_SCALE"].ToString().Trim() != "" ? int.Parse(row["NUMERIC_SCALE"].ToString().Trim()) : -1,
                          autoincrement);

                        switch (Pixysoft.EnumCoder.ToEnum<SQLiteDataTypeCollection>(typename))
                        {
                            case SQLiteDataTypeCollection.INTEGER:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Precision = typeschema.Precision > 38 ? 38 : typeschema.Precision;
                                    typeschema.Scale = -1;

                                    break;
                                }
                            case SQLiteDataTypeCollection.REAL:
                            case SQLiteDataTypeCollection.NUMERIC:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Precision = typeschema.Precision > 38 ? 38 : typeschema.Precision;
                                    typeschema.Scale = typeschema.Scale > typeschema.Precision ? typeschema.Precision : typeschema.Scale;

                                    break;
                                }

                            case SQLiteDataTypeCollection.TEXT:
                                {
                                    typeschema.Length = typeschema.Length > 2147483647 ? 2147483647 : typeschema.Length;
                                    typeschema.Precision = -1;
                                    typeschema.Scale = -1;

                                    break;
                                }
                            case SQLiteDataTypeCollection.DATETIME:
                            case SQLiteDataTypeCollection.NONE:
                            default:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Precision = -1;
                                    typeschema.Scale = -1;
                                    break;
                                }
                        }

                        columnSchema.DataType = typeschema;

                        tbschema.Columns.Add(columnSchema);
                    }
                }

            }
        }

        internal void GetConstraints(SQLiteConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Indexes");

                foreach (TableSchema tbschema in schema.Tables)
                {
                    DataRow[] rows = Pixysoft.DataTableCoder.Instance.GetRows(table, "TABLE_NAME", tbschema.TableName);

                    foreach (DataRow row in rows)
                    {
                        bool unique = bool.Parse(row["UNIQUE"].ToString());

                        bool primarykey = bool.Parse(row["PRIMARY_KEY"].ToString());

                        string constname = row["INDEX_NAME"].ToString().ToUpper();

                        if (unique && primarykey)
                        {
                            //primary key

                            PrimaryKeySchema pkSchema = new PrimaryKeySchema();
                            pkSchema.ConsName = constname;
                            pkSchema.IndexName = constname;
                            pkSchema.TableName = tbschema.TableName;
                            schema.PrimaryKeys.Add(pkSchema);
                        }
                        else if (unique)
                        {
                            //unique

                            UniqueSchema uSchema = new UniqueSchema();
                            uSchema.ConsName = constname;
                            uSchema.IndexName = constname;
                            uSchema.TableName = tbschema.TableName;
                            schema.Uniques.Add(uSchema);
                        }
                        else
                        {
                            //index 

                            IndexSchema iSchema = new IndexSchema();
                            iSchema.ConsName = constname;
                            iSchema.IndexName = constname;
                            iSchema.TableName = tbschema.TableName;
                            schema.Indexs.Add(iSchema);
                        }
                    }
                }

                table = conn.GetSchema("IndexColumns");

                foreach (DataRow row in table.Rows)
                {
                    string tbname = row["TABLE_NAME"].ToString().ToUpper().Trim();

                    string constName = row["INDEX_NAME"].ToString().ToUpper().Trim();

                    string columnName = row["COLUMN_NAME"].ToString().Trim().ToUpper();

                    int index = int.Parse(row["ORDINAL_POSITION"].ToString().ToUpper().Trim());

                    PrimaryKeySchema pkSchema = schema.GetPrimaryKeyByName(tbname, constName);

                    if (pkSchema != null)
                    {
                        SchemaUtility.AddToList<string>(pkSchema.ColumnNames, columnName, index);

                        continue;
                    }

                    UniqueSchema uSchema = schema.GetUniqueByName(constName);

                    if (uSchema != null)
                    {
                        SchemaUtility.AddToList<string>(uSchema.ColumnNames, columnName, index);

                        continue;
                    }

                    IndexSchema iSchema = schema.GetIndexByName(constName);

                    if (iSchema != null)
                    {
                        SchemaUtility.AddToList<string>(iSchema.ColumnNames, columnName, index);

                        continue;
                    }
                }
            }
        }

        internal void GetForeignKeys(SQLiteConnection oconn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((oconn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    oconn.Open();
                }

                List<string> rcollist = new List<string>();
                string rtable = null;
                ForeignKeySchema formerschema = null;

                DataTable table = oconn.GetSchema("ForeignKeys");

                foreach (DataRow row in table.Rows)
                {
                    if (row["CONSTRAINT_TYPE"].ToString().ToUpper().Trim() != "FOREIGN KEY")
                        continue;

                    string constname = row["CONSTRAINT_NAME"].ToString().ToUpper().Trim();

                    string ftablename = row["TABLE_NAME"].ToString().Trim().ToUpper();

                    string rtablename = row["FKEY_TO_TABLE"].ToString().Trim().ToUpper();

                    string fcolname = row["FKEY_FROM_COLUMN"].ToString().Trim().ToUpper();

                    string rcolname = row["FKEY_TO_COLUMN"].ToString().Trim().ToUpper();

                    int colindex = int.Parse(row["FKEY_FROM_ORDINAL_POSITION"].ToString().Trim().ToUpper());


                    ForeignKeySchema fkschema = schema.GetForeignKeyByName(constname);

                    if (fkschema == null)
                    {
                        if (rcollist.Count > 0 && formerschema != null)
                        {
                            IConstraintSchema constschema = schema.GetConstraint(rtable, rcollist);

                            if (constschema != null)
                            {
                                string pkconstname = constschema.ConsName;
                                formerschema.PkeyConsName = pkconstname;

                                //2010-08-01 支持最新的fk结构

                                formerschema.PkTableName = constschema.TableName;
                                foreach (string column in constschema.ColumnNames)
                                    formerschema.PkColumnNames.Add(column);
                            }
                        }

                        fkschema = new ForeignKeySchema();
                        schema.ForeignKeys.Add(fkschema);
                        rcollist = new List<string>();
                        rtable = null;
                        formerschema = null;
                    }

                    fkschema.ConsName = constname;
                    fkschema.FkTableName = ftablename;
                    SchemaUtility.AddToList<string>(fkschema.FkColumnNames, fcolname, colindex);
                    SchemaUtility.AddToList<string>(rcollist, rcolname, colindex);
                    rtable = rtablename;
                    formerschema = fkschema;
                }

                if (rcollist.Count > 0 && formerschema != null)
                {
                    IConstraintSchema constschema = schema.GetConstraint(rtable, rcollist);

                    if (constschema != null)
                    {
                        string pkconstname = constschema.ConsName;
                        formerschema.PkeyConsName = pkconstname;


                        //2010-08-01 支持最新的fk结构

                        formerschema.PkTableName = constschema.TableName;
                        foreach (string column in constschema.ColumnNames)
                            formerschema.PkColumnNames.Add(column);
                    }
                }

                if (oconn.State == ConnectionState.Open)
                    oconn.Close();
            }
        }
    }
}
